<%@codepage="65001"%>
<%session.codepage="65001"%>
<!-- #include virtual=/inc/dbConn.asp -->
<!-- #include virtual=/inc/function.asp -->
<!-- #include virtual=/inc/action.header.inc.asp -->

<%
	'## 파라미터 변수 선언부
	stdYear		= Request("stYear")
	stdInOut		= Request("findInOut")
	
	If stdYear = "" Then stdYear = Left(Date(),4) End If 
	If stdInOut = "" Then stdInOut = "IN" End If 
	
	SQLTitle = "-- ■■■■ 통계 관리 그리드 List up ■■■■"
	
	'## 검색조건에 따른 조건절 만들기 
	
	If stdInOut = "IN" then 

		SQL = "SELECT  company, " & _
				"				ISNULL([" & stdYear & "-01],0) M01,ISNULL([" & stdYear & "-02],0) M02, ISNULL([" & stdYear & "-03],0) M03,ISNULL([" & stdYear & "-04],0) M04,ISNULL([" & stdYear & "-05],0) M05,ISNULL([" & stdYear & "-06],0) M06,   " & _
				"				ISNULL([" & stdYear & "-07],0) M07,ISNULL([" & stdYear & "-08],0) M08, ISNULL([" & stdYear & "-09],0) M09,ISNULL([" & stdYear & "-10],0) M10,ISNULL([" & stdYear & "-11],0) M11,ISNULL([" & stdYear & "-12],0) M12  " & _       
				"		FROM (" & _             
				"				SELECT LEFT( A.incomeDt ,7) mnth, B.company company, SUM(isnull (A.orgAmount,0 )) amt " & _             
				"				FROM tblIncome A  " & _  
				"							LEFT JOIN tblCompany B ON A.compCd =B. compCd " & _  
				"				WHERE A.incomeDt LIKE '" & stdYear & "%' AND A .flag= 'Y' " & _  
				"				GROUP BY LEFT(A .incomeDt, 7), B.company  " & _  
				"				UNION ALL  " & _  
				"				SELECT LEFT( A.incomeDt ,7) mnth, 'Y^총액' company, SUM(isnull(A.orgAmount,0 )) amt " & _             
				"				FROM tblIncome A  " & _  
				"				WHERE A.incomeDt LIKE '" & stdYear & "%' AND A .flag= 'Y' " & _  
				"				GROUP BY LEFT(A .incomeDt, 7)  " & _  
				"				UNION ALL  " & _  
				"				SELECT LEFT( A.planDt ,7) mnth, 'Z^지출총액' as company, SUM(isnull(A.amount,0 )) amt  " & _  
				"				FROM tblPayment A  " & _  
				"				WHERE A.planDt LIKE '" & stdYear & "%' AND A .flag= 'Y'  " & _  
				"				GROUP BY LEFT(A.planDt, 7) " & _  
				"				UNION ALL  " & _  
				"				SELECT I.mnth, 'Z^총수익' as company, sum(inAmt)-sum(outAmt) FROM " & _  
				"				( " & _  
				"					SELECT LEFT(incomeDt ,7) mnth, SUM(isnull(orgAmount,0)) inAmt FROM tblIncome A WHERE incomeDt LIKE '" & stdYear & "%' AND flag= 'Y' GROUP BY LEFT(incomeDt, 7) " & _     
				"				) I LEFT JOIN ( " & _  
				"					SELECT LEFT(planDt ,7) mnth,  SUM(isnull(amount,0)) outAmt FROM tblPayment A WHERE planDt LIKE '" & stdYear & "%' AND flag= 'Y' GROUP BY LEFT(planDt, 7) " & _    
				"				) O ON I.mnth=O.mnth " & _  
				"				GROUP BY I.mnth " & _  
				"				UNION ALL  " & _  
				"				SELECT LEFT( A.incomeDt ,7) mnth, 'Y^생산1팀' company, SUM(isnull(A.orgAmount,0 )) amt " & _             
				"				FROM tblIncome A  " & _  
				"				WHERE A.incomeDt LIKE '" & stdYear & "%' AND A .flag= 'Y' AND A.part='10' " & _  
				"				GROUP BY LEFT(A .incomeDt, 7)  " & _  
				"				UNION ALL  " & _  
				"				SELECT LEFT( A.incomeDt ,7) mnth, 'Y^생산2팀' company, SUM(isnull(A.orgAmount,0 )) amt " & _             
				"				FROM tblIncome A  " & _  
				"				WHERE A.incomeDt LIKE '" & stdYear & "%' AND A .flag= 'Y' AND A.part='20' " & _  
				"				GROUP BY LEFT(A .incomeDt, 7)  " & _  
				"			) K " & _  
				"		PIVOT (SUM(amt) FOR mnth IN ([" & stdYear & "-01] ,[" & stdYear & "-02], [" & stdYear & "-03],[" & stdYear & "-04] ,[" & stdYear & "-05],[" & stdYear & "-06], " & _
				"					[" & stdYear & "-07] ,[" & stdYear & "-08], [" & stdYear & "-09],[" & stdYear & "-10] ,[" & stdYear & "-11], [" & stdYear & "-12] ) " & _
				"		) pvt " & _
				" ORDER BY company "
	else
		SQL = "SELECT  company, " & _
				"				ISNULL([" & stdYear & "-01],0) M01,ISNULL([" & stdYear & "-02],0) M02, ISNULL([" & stdYear & "-03],0) M03,ISNULL([" & stdYear & "-04],0) M04,ISNULL([" & stdYear & "-05],0) M05,ISNULL([" & stdYear & "-06],0) M06,   " & _
				"				ISNULL([" & stdYear & "-07],0) M07,ISNULL([" & stdYear & "-08],0) M08, ISNULL([" & stdYear & "-09],0) M09,ISNULL([" & stdYear & "-10],0) M10,ISNULL([" & stdYear & "-11],0) M11,ISNULL([" & stdYear & "-12],0) M12  " & _       
				"		FROM (" & _             
				"				SELECT LEFT( A.planDt ,7) mnth, B.accountNm company, SUM(isnull (A.amount,0 )) amt " & _             
				"				FROM tblPayment A  " & _  
				"							LEFT JOIN tblAccount B ON A.accountCd =B. accountCd " & _  
				"				WHERE A.planDt LIKE '" & stdYear & "%' AND A .flag= 'Y' " & _  
				"				GROUP BY LEFT(A.planDt, 7), B.accountNm  " & _  
				"				UNION ALL  " & _  
				"				SELECT LEFT( A.incomeDt ,7) mnth, 'Z^수입총액' company, SUM(isnull(A.orgAmount,0 )) amt " & _             
				"				FROM tblIncome A  " & _  
				"				WHERE A.incomeDt LIKE '" & stdYear & "%' AND A .flag= 'Y' " & _  
				"				GROUP BY LEFT(A .incomeDt, 7)  " & _  
				"				UNION ALL  " & _  
				"				SELECT LEFT( A.planDt ,7) mnth, 'Y^총액' as company, SUM(isnull(A.amount,0 )) amt  " & _  
				"				FROM tblPayment A  " & _  
				"				WHERE A.planDt LIKE '" & stdYear & "%' AND A .flag= 'Y'  " & _  
				"				GROUP BY LEFT(A.planDt, 7) " & _  
				"				UNION ALL  " & _  
				"				SELECT I.mnth, 'Z^총수익' as company, sum(inAmt)-sum(outAmt) FROM " & _  
				"				( " & _  
				"					SELECT LEFT(incomeDt ,7) mnth, SUM(isnull(orgAmount,0)) inAmt FROM tblIncome A WHERE incomeDt LIKE '" & stdYear & "%' AND flag= 'Y' GROUP BY LEFT(incomeDt, 7) " & _     
				"				) I LEFT JOIN ( " & _  
				"					SELECT LEFT(planDt ,7) mnth,  SUM(isnull(amount,0)) outAmt FROM tblPayment A WHERE planDt LIKE '" & stdYear & "%' AND flag= 'Y' GROUP BY LEFT(planDt, 7) " & _    
				"				) O ON I.mnth=O.mnth " & _  
				"				GROUP BY I.mnth " & _  
				"				UNION ALL  " & _  
				"				SELECT LEFT( A.planDt ,7) mnth, 'Y^생산1팀' as company, SUM(isnull(A.amount,0 )) amt  " & _  
				"				FROM tblPayment A  " & _  
				"				WHERE A.planDt LIKE '" & stdYear & "%' AND A .flag= 'Y' AND A.part='10' " & _  
				"				GROUP BY LEFT(A.planDt, 7) " & _  
				"				UNION ALL  " & _  
				"				SELECT LEFT( A.planDt ,7) mnth, 'Y^생산2팀' as company, SUM(isnull(A.amount,0 )) amt  " & _  
				"				FROM tblPayment A  " & _  
				"				WHERE A.planDt LIKE '" & stdYear & "%' AND A .flag= 'Y' AND A.part='20' " & _  
				"				GROUP BY LEFT(A.planDt, 7) " & _  
				"			) K " & _  
				"		PIVOT (SUM(amt) FOR mnth IN ([" & stdYear & "-01] ,[" & stdYear & "-02], [" & stdYear & "-03],[" & stdYear & "-04] ,[" & stdYear & "-05],[" & stdYear & "-06], " & _
				"					[" & stdYear & "-07] ,[" & stdYear & "-08], [" & stdYear & "-09],[" & stdYear & "-10] ,[" & stdYear & "-11], [" & stdYear & "-12] ) " & _
				"		) pvt " & _
				" ORDER BY company "
	end if 	

'Response.write sql
'Response.end
	If xMode="debug" Then 
		Call showDebug(SQLTitle,SQL) 
	End If 

	Set Rs=Server.CreateObject("ADODB.RecordSet")
	Rs.open SQL, DbCon, 1
	
	Response.write "<Statistics>" & vbcrlf
	If stdInOut = "OUT" then
		ttlAmount = aFieldValue("SELECT sum(amount) FROM tblPayment A WHERE A.planDt LIKE '" & stdYear & "%' AND A .flag= 'Y'")   
	else 
		ttlAmount = aFieldValue("SELECT sum(orgAmount) FROM tblIncome A WHERE A.incomeDt LIKE '" & stdYear & "%' AND A .flag= 'Y'")   
	end if  

	If Rs.eof Or Rs.bof Then 
	Else 
		Do Until Rs.eof Or Rs.bof
			m01 = rs("M01")
			m02 = rs("M02")
			m03 = rs("M03")
			m04 = rs("M04")
			m05 = rs("M05")
			m06 = rs("M06")
			m07 = rs("M07")
			m08 = rs("M08")
			m09 = rs("M09")
			m10 = rs("M10")
			m11 = rs("M11")
			m12 = rs("M12")
			
			total = CDbl(m01)+CDbl(m02)+CDbl(m03)+CDbl(m04)+CDbl(m05)+CDbl(m06)+CDbl(m07)+CDbl(m08)+CDbl(m09)+CDbl(m10)+CDbl(m11)+CDbl(m12)
			ttlRate = round(((total/CDbl(ttlAmount))*100),2) & "%"	 

			If total <> "" Then total = FormatNumber(total,0) End If 
			Response.write "	<Stat>" & vbcrlf
				Call mkXMLNode("company", rs("company"), 2)
				Call mkXMLNode("m01", FormatNumber(m01,0), 2)
				Call mkXMLNode("m02", FormatNumber(m02,0), 2)
				Call mkXMLNode("m03", FormatNumber(m03,0), 2)
				Call mkXMLNode("m04", FormatNumber(m04,0), 2)
				Call mkXMLNode("m05", FormatNumber(m05,0), 2)
				Call mkXMLNode("m06", FormatNumber(m06,0), 2)
				Call mkXMLNode("m07", FormatNumber(m07,0), 2)
				Call mkXMLNode("m08", FormatNumber(m08,0), 2)
				Call mkXMLNode("m09", FormatNumber(m09,0), 2)
				Call mkXMLNode("m10", FormatNumber(m10,0), 2)
				Call mkXMLNode("m11", FormatNumber(m11,0), 2)
				Call mkXMLNode("m12", FormatNumber(m12,0), 2)
				Call mkXMLNode("total", total, 2)
				Call mkXMLNode("ttlRate", ttlRate, 2)
				Call mkXMLNode("ttlAmount", ttlAmount, 2)
			Response.write "	</Stat>" & vbcrlf

		Rs.movenext
		Loop 
	End If
	Response.write "</Statistics>" & vbcrlf
'	Call rsWithXML("Save OK!")
%>